<?php
namespace app\common\library;

use think\App;
use think\Config;
use think\Log;
use think\Env;
use think\Db;

use app\admin\model\sport\Area as AreaModel;
use app\admin\model\sport\ScoreType as ScoreTypeModel;
use app\admin\model\sport\Score as ScoreModel;
use app\common\library\Score as ScoreLib;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
use PhpOffice\PhpSpreadsheet\Style\Protection;

/**
 * 分数计算类
 */
class Batch
{
    protected $scoreTypes = [
        'middle'  => '中考体育',
        'senior'  => '高考体育',
    ];

    protected $sexs = [
        '男'  => '1',
        '女'  => '2',
    ];

    public function createTemplate($areaId, $type, $filename)
    {
        try {
            if ($type == 'middle') {
                $this->createXlsMiddle($areaId, $type, $filename);
            }
            if ($type == 'senior') {
                $this->createXlsSenior($areaId, $type, $filename);
            }
            return true;
        } catch (\Exception $e) {
            return false; 
        }
    }

    public function handleExcel($sourcefilename)
    {
        $res = [];
        $reader = IOFactory::createReader('Xlsx');
        $spreadsheet = $reader->load($sourcefilename);
        $worksheet = $spreadsheet->getActiveSheet();
        $highestRow  = $worksheet->getHighestRow(); // 取得总行数
        $highestColumn = $worksheet->getHighestColumn(); // 取得总列数
        $highestColumnIndex = Coordinate::columnIndexFromString($highestColumn); // 取得总列数
        $excelData = [];
        // 计算的起始行
        $startRow = 3;
        // 数据获取的起始列
        $startCol = 1;
        $i = 0;
        $header = $worksheet->getCell('A1')->getValue();
        $areaId = 0;
        $type = '';
        for ($row = $startRow; $row <= $highestRow; $row++) {
            for ($col = $startCol; $col <= $highestColumnIndex; $col++) { 
                $excelData[$i][] = (string)$worksheet->getCellByColumnAndRow($col, $row)->getValue();
            }
            $i++;
        }
        $lastData = $excelData[count($excelData) - 1];
        // 最后一行的内容, 获取区域ID和类别
        if (!empty($lastData[0])) {
            $areaId = $lastData[0];
        }
        if (!empty($lastData[1])) {
            $type = $lastData[1];
        }
        $totalRecords = 0;
        foreach ($excelData as $rowData) {
            // 起始列
            $startIndex = 0;
            // 姓名
            $name = $rowData[$startIndex];
            $startIndex++;
            // 性别
            $sex = $rowData[$startIndex++];
            if ($type == 'middle') {
                // 体育项
                $item1 = $rowData[$startIndex++];
                // 成绩
                $score1 = $rowData[$startIndex++];
                if (!empty($name) && !empty($sex) && !empty($item1)) {
                    $totalRecords++;
                }
            } else if ($type == 'senior') {
                // 体育项
                $item1 = $rowData[$startIndex++];
                $item2 = $rowData[$startIndex++];
                $item3 = $rowData[$startIndex++];
                $item4 = $rowData[$startIndex++];
                if (!empty($name) && !empty($sex) && $sex != 'senior') {
                    $totalRecords++;
                }
            }
        }

        return [$header, $areaId, $type, $totalRecords];
    
    }

    public function calSeniorScore($sourcefilename, $filename)
    {
        // 导入表格分析
        $reader = IOFactory::createReader('Xlsx');
        $spreadsheet = $reader->load($sourcefilename);
        $worksheet = $spreadsheet->getActiveSheet();
        $highestRow  = $worksheet->getHighestRow(); // 取得总行数
        $highestColumn = $worksheet->getHighestColumn(); // 取得总列数
        $highestColumnIndex = Coordinate::columnIndexFromString($highestColumn); // 取得总列数
        $excelData = [];
        // 计算的起始行
        $startRow = 3;

        // echo $startRow;exit;  
        // 数据获取的起始列
        $startCol = 1;
        $i = 0;
        // 获取其他信息
        $header = $worksheet->getCell('A1')->getValue();
        $areaId = 0;
        $type = '';
        for ($row = $startRow; $row <= $highestRow; $row++) {
            for ($col = $startCol; $col <= $highestColumnIndex; $col++) { 
                $excelData[$i][] = (string)$worksheet->getCellByColumnAndRow($col, $row)->getValue();
            }
            $i++;
        }

        $lastData = $excelData[count($excelData) - 1];
        // 最后一行的内容, 获取区域ID和类别
        if (!empty($lastData[0])) {
            $areaId = $lastData[0];
        }
        if (!empty($lastData[1])) {
            $type = $lastData[1];
        }
        // 获取第2行的表头的体育项
        // 体育项所在行
        // 由于表头增加了回车换行
        $itemRow = 2;
        $item1 = $worksheet->getCell('C' . $itemRow)->getValue();
        $item2 = $worksheet->getCell('D' . $itemRow)->getValue();
        $item3 = $worksheet->getCell('E' . $itemRow)->getValue();
        $item4 = $worksheet->getCell('F' . $itemRow)->getValue();

        $item1s = explode("\n", $item1);
        $item1 = $item1s[0];
        $item2s = explode("\n", $item2);
        $item2 = $item2s[0];
        $item3s = explode("\n", $item3);
        $item3 = $item3s[0];
        $item4s = explode("\n", $item4);
        $item4 = $item4s[0];

        // 取出所有体育项记录
        $sportModel = ScoreModel::where('sport_area_id', $areaId)
          ->where('score_type', $type)
          ->order('weigh', 'desc')
          ->select();
        $sports = [];
        foreach($sportModel as $si) {
            $sports[$si->sex . "-" . $si->project_name] = [
                'score_setting' => $si->score_setting,
                'unit' => $si->unit,
                'show_unit' => empty($si->show_unit) ? $si->unit : $si->show_unit,
            ];
        }
        // 查找区域中中考和高考的评级设置以及中考的固定分
        $areaModel = AreaModel::where('id', $areaId)->where('status', 'normal')->find();
        // 判断有无区域的数据
        if (empty($areaModel)) {
            Log::write('无对应区域的设置');
            return;
        }
        $seniorLevelSetting = $areaModel->high_level_str;

        $scoreLib = new ScoreLib();
        $newExcelData = [];
        // 查找的列的索引
        foreach ($excelData as $rowData) {
            $data = []; 
            // 起始列
            $startIndex = 0;
            // 姓名
            $name = $rowData[$startIndex];
            $startIndex++;
            // 性别
            $sex = $rowData[$startIndex++];
            $score1 = $rowData[$startIndex++];
            $score2 = $rowData[$startIndex++];
            $score3 = $rowData[$startIndex++];
            $score4 = $rowData[$startIndex++];
            
            // 得分
            $point1 = 0;
            $point2 = 0;
            $point3 = 0;
            $point4 = 0;
            // 总得分
            $totalPoint = 0;
            // 如果成绩栏位留的是空时
            $score1 = $score1 != '' ? $score1 : '0';
            $score2 = $score2 != '' ? $score2 : '0';
            $score3 = $score3 != '' ? $score3 : '0';
            $score4 = $score4 != '' ? $score4 : '0';
            if (!empty($sex)
              && $sex != 'senior' // 因为最后会有特殊字符，所以要屏蔽掉
              && $score1 != ''
              && $score2 != ''
              && $score3 != ''
              && $score4 != ''
            ) {
                $sextmp = $this->sexs[$sex];
                // echo $sextmp . "--" . $item1 . PHP_EOL;
                // 计算成绩
                if (isset($sports[$sextmp . "-" . $item1])) {
                    // 转换输入的成绩和要显示的
                    $scoreInfo = $sports[$sextmp . "-" . $item1];
                    $scoreRes = $scoreLib->convertScore($score1, $scoreInfo['unit'], $scoreInfo['show_unit']);
                    $calScore = $scoreRes[0];
                    // 计算完毕后把展示的重新赋值回去 
                    $score1 = strval($scoreRes[1] . " ");
                    $point1 = $scoreLib->calPoint($scoreInfo['score_setting'], $calScore);
                    $totalPoint += $point1;
                }
                // echo $sextmp . "--" . $item2 . PHP_EOL;
                if (isset($sports[$sextmp . "-" . $item2])) {
                    $scoreInfo = $sports[$sextmp . "-" . $item2];
                    $scoreRes = $scoreLib->convertScore($score2, $scoreInfo['unit'], $scoreInfo['show_unit']);
                    $calScore = $scoreRes[0];
                    $score2 = strval($scoreRes[1] . " ");
                    $point2 = $scoreLib->calPoint($scoreInfo['score_setting'], $calScore);
                    $totalPoint += $point2;
                }
                // echo $sextmp . "--" . $item3 . PHP_EOL;
                if (isset($sports[$sextmp . "-" . $item3])) {
                    $scoreInfo = $sports[$sextmp . "-" . $item3];
                    $scoreRes = $scoreLib->convertScore($score3, $scoreInfo['unit'], $scoreInfo['show_unit']);
                    $calScore = $scoreRes[0];
                    $score3 = strval($scoreRes[1] . " ");
                    $point3 = $scoreLib->calPoint($scoreInfo['score_setting'], $calScore);
                    $totalPoint += $point3;
                }
                // echo $sextmp . "--" . $item4 . PHP_EOL;
                if (isset($sports[$sextmp . "-" . $item4])) {
                    $scoreInfo = $sports[$sextmp . "-" . $item4];
                    $scoreRes = $scoreLib->convertScore($score4, $scoreInfo['unit'], $scoreInfo['show_unit']);
                    $calScore = $scoreRes[0];
                    $score4 = strval($scoreRes[1] . " " );
                    $point4 = $scoreLib->calPoint($scoreInfo['score_setting'], $calScore);
                    $totalPoint += $point4;
                }
                // echo $totalPoint . "--" . PHP_EOL;
                $calRes = $scoreLib->calSeniorScore(null, null, $seniorLevelSetting, $totalPoint);
                $data[] = $name;
                $data[] = $sex;
                $data[] = $score1;
                $data[] = $point1;

                $data[] = $score2;
                $data[] = $point2;

                $data[] = $score3;
                $data[] = $point3;

                $data[] = $score4;
                $data[] = $point4;

                $data[] = $point1 + $point2 + $point3 + $point4;
                $data[] = $calRes['level_code'] . '('. $calRes['level_name'] . ')';
                // $data[] = $calRes['level_name'];
                $newExcelData[] = $data;
            }
        }
        // var_dump($newExcelData);

        $this->createXlsSenior($areaId, $type, $filename, $newExcelData);
    
    }

    // 创建高考Xls
    private function createXlsSenior($areaId, $type, $filename, $excelData = null)
    {
        $areaModel = AreaModel::where('id', $areaId)->find();
        $sheetName = "钧韬竞速" . $areaModel->area_name . $this->scoreTypes[$type] ."成绩换算表";
        // 查询体育项, 不分男女查找
        $sportScores = ScoreModel::where('sport_area_id', $areaId)->where('score_type', 'senior')
                                                                  ->where('status', 'normal')
                                                                  ->order('weigh', 'desc')
                                                                  ->select();
        $items = [];
        // 样例行
        $showExample = [];
        foreach($sportScores as $st) {
            if (!in_array($st->project_name, $items)) {
                $items[] = $st->project_name;
                // 如果有成绩时
                if (!empty($excelData)) {
                    $items[] = '得分';
                }
                if (empty($excelData)) {
                    $showExample[] = '例如：'.$st->show_val;
                }
            }
        }
        // 有成绩时补充的后面的列
        $colNames = [];
        if (!empty($excelData)) {
            $colNames = ['总分', '评级'];
        }
        $rowArray = array_merge(['姓名','性别'], $items, $colNames);
        //实例化一个新的excel文档
        $newExcel = new Spreadsheet();
        //获取当前操作sheet的对象
        $objSheet = $newExcel->getActiveSheet();
        $objSheet->setTitle($sheetName);//设置当前sheet的标题
        // 高考模版
        $rowNum = 1; 
        // 以下是首行样式
        $objSheet->setCellValue('A1', $sheetName);
        // 结束的列
        $colEndName = "F";
        // 有计算结果用
        if (!empty($excelData)) {
            $colEndName = "L";
        }
        // 增加输入的注释说明
        if (empty($excelData)) {
            $objSheet->setCellValue('G1', '分钟的输入格式以“.”隔开,例如：2分56秒12输入2.56.12  3分10秒输入3.10');
            $objSheet->getStyle('G1')->applyFromArray([
                'font' => [
                    'bold' => true,
                    'size' => 12,
                    'color'=>['argb' => 'fd3838']
                ],
            ]);
            /*
            // 样例行
            $objSheet->setCellValue('G3', '参考样例行，请勿编辑和删除');
            $objSheet->getStyle('G3')->applyFromArray([
                'font' => [
                    'bold' => true,
                    'size' => 12,
                    'color'=>['argb' => 'fd3838']
                ],
            ]);
            */
        }
        // 合并单元格
        $objSheet->mergeCells('A' . $rowNum.':'.$colEndName.''.$rowNum);
        // 行样式
        $objSheet->getStyle('A'.$rowNum.':'.'A'.$rowNum)->applyFromArray([
            'font' => [
                'bold' => true,
                'size' => 18,
                'color'=>['argb' => '7030a0']
            ],
        ]);
        // 设置合并列的行高
        $objSheet->getRowDimension($rowNum)->setRowHeight(40);
        $objSheet->getStyle('A' . $rowNum)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $objSheet->getStyle('A' . $rowNum)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        // 第二行
        $rowNum++;
        // 固定首行内容
        if (empty($excelData)) {
            $rowExampleArray = array_merge(['例如：张三','例如：男'], $showExample);
            foreach ($rowArray as $key => &$ra) {
                // 使用 \n 分隔标题和示例，但不直接合并
                $parts = [$ra, $rowExampleArray[$key]];
                $ra = implode("\n", $parts);
            }
            unset($ra);
            $objSheet->fromArray($rowArray, NULL, 'A' . $rowNum);
            
            // 设置自动换行
            $objSheet->getStyle('A' . $rowNum . ':' . $colEndName . $rowNum)
                    ->getAlignment()
                    ->setWrapText(true);
                    
            // 使用富文本格式设置不同的字体大小
            foreach(range('A', $colEndName) as $col) {
                $cell = $objSheet->getCell($col . $rowNum);
                $text = $cell->getValue();
                if (strpos($text, "\n") !== false) {
                    list($title, $example) = explode("\n", $text);
                    $rich = new \PhpOffice\PhpSpreadsheet\RichText\RichText();
                    $part1 = $rich->createTextRun($title . "\n");
                    $part1->getFont()->setSize(14)->setColor(new \PhpOffice\PhpSpreadsheet\Style\Color('FFFFFF')); // 标题字体大小
                    $part2 = $rich->createTextRun($example);
                    $part2->getFont()->setSize(10)->setColor(new \PhpOffice\PhpSpreadsheet\Style\Color('FFFFFF')); // 示例字体大小
                    $cell->setValue($rich);
                }
            }
            // 设置行高
            $objSheet->getRowDimension($rowNum)->setRowHeight(36);
        } else {
            $objSheet->fromArray($rowArray, NULL, 'A' . $rowNum);
            // 设置行高
            $objSheet->getRowDimension($rowNum)->setRowHeight(26);
        }

        $objSheet->getStyle('A' . $rowNum . ':' . $colEndName . $rowNum)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('7030a0');
        $objSheet->getStyle('A' . $rowNum . ':' . $colEndName . $rowNum)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $objSheet->getStyle('A' . $rowNum . ':' . $colEndName . $rowNum)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $objSheet->getStyle('A' . $rowNum . ':' . $colEndName . $rowNum)->applyFromArray([
            'font' => [
                'bold' => true,
                'size' => 14,
                'color'=>['argb' => 'ffffff']
            ],
        ]);
        $objSheet->getStyle('A' . $rowNum . ':' . $colEndName . $rowNum)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        $objSheet->freezePane('A' . ($rowNum + 1));

        // 开始后面的行
        $rowNum++;
        // 当没有计算结果时
        if (empty($excelData)) { 
            // 生成下拉
            for($row=$rowNum; $row< 1000; $row++) {
                // 生成黑线
                $objSheet->getStyle('A' . $row . ':' . $colEndName . $row)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
                $this->setCell($objSheet, 'B' . $row, ["男", "女"]);
            }
            // 保存区域和考试类别
            $objSheet->setCellValue('A8130', $areaId);
            $objSheet->setCellValue('B8130', $type);
        } else {
            $font = [
                'font' => [
                    'bold' => true,
                    'size' => 11,
                    'color'=>['argb' => 'ff0000']
                 ],
            ];
            $rowCount = count($excelData) + $rowNum;
            // 有计算结果时
            $objSheet->fromArray($excelData, NULL, 'A' . $rowNum);
            for ($row = $rowNum; $row < $rowCount; $row++) {
                // 生成黑线
                $objSheet->getStyle('A' . $row . ':' . $colEndName . $row)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
                // 设置背景色 
                $objSheet->getStyle('C' . $row)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('b9cce4');
                $objSheet->getStyle('E' . $row)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('b9cce4');
                $objSheet->getStyle('G' . $row)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('b9cce4');
                $objSheet->getStyle('I' . $row)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('b9cce4');
                // 设置分数为红色
                $objSheet->getStyle('D'.$row)->applyFromArray($font);
                $objSheet->getStyle('F'.$row)->applyFromArray($font);
                $objSheet->getStyle('H'.$row)->applyFromArray($font);
                $objSheet->getStyle('J'.$row)->applyFromArray($font);
                $objSheet->getStyle('K'.$row)->applyFromArray($font);
                $objSheet->getStyle('L'.$row)->applyFromArray($font);
            }
        }

        // 设置整体列宽
        $objSheet->getDefaultColumnDimension()->setWidth(12);
        // 设置整体居中
        $objSheet->getParent()->getDefaultStyle()->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $writer = new Xlsx($newExcel);
        $writer->save($filename);
    
    }
    
    // 中考计算
    public function calMiddleScore($sourcefilename, $filename)
    {
        // 导入表格分析
        /*
        // 待分析的xlsx
        $sourcefilename="/Users/sss020/Downloads/sports.xlsx";
        // 结果的xlsx
        $filename="/Users/sss020/Downloads/sports_res.xlsx";
        */
        $reader = IOFactory::createReader('Xlsx');
        $spreadsheet = $reader->load($sourcefilename);
        $worksheet = $spreadsheet->getActiveSheet();
        $highestRow  = $worksheet->getHighestRow(); // 取得总行数
        $highestColumn = $worksheet->getHighestColumn(); // 取得总列数
        $highestColumnIndex = Coordinate::columnIndexFromString($highestColumn); // 取得总列数
        $excelData = [];
        // 计算的起始行
        $startRow = 3;
        // 数据获取的起始列
        $startCol = 1;
        $i = 0;
        // 获取其他信息
        $header = $worksheet->getCell('A1')->getValue();
        $areaId = 0;
        $type = '';
        for ($row = $startRow; $row <= $highestRow; $row++) {
            for ($col = $startCol; $col <= $highestColumnIndex; $col++) { 
                $excelData[$i][] = (string)$worksheet->getCellByColumnAndRow($col, $row)->getValue();
            }
            $i++;
        }
        $lastData = $excelData[count($excelData) - 1];
        // 最后一行的内容, 获取区域ID和类别
        if (!empty($lastData[0])) {
            $areaId = $lastData[0];
        }
        if (!empty($lastData[1])) {
            $type = $lastData[1];
        }
        // 取出所有体育项记录
        $sportModel = ScoreModel::where('sport_area_id', $areaId)->where('score_type', $type)->select();
        $sports = [];
        foreach($sportModel as $si) {
            $sports[$si->sex . "-" . $si->project_name] = [
                'score_setting' => $si->score_setting,
                'unit' => $si->unit,
                'show_unit' => empty($si->show_unit) ? $si->unit : $si->show_unit,
            ];
        }
        // 查找区域中中考和高考的评级设置以及中考的固定分
        $areaModel = AreaModel::where('id', $areaId)->where('status', 'normal')->find();
        // 判断有无区域的数据
        if (empty($areaModel)) {
            Log::write('无对应区域的设置');
            return;
        }
        $fixedScore = $areaModel->middle_fixed_score;
        $middlePercent = $areaModel->middle_percent;
        $middleLevelSetting = $areaModel->level_str;
        $seniorLevelSetting = $areaModel->high_level_str;

        $scoreLib = new ScoreLib();
        $newExcelData = [];
        // 查找的列的索引
        foreach ($excelData as $rowData) {
            $data = []; 
            // 起始列
            $startIndex = 0;
            // 班级
            $grade = $rowData[$startIndex];
            $startIndex++;
            // 姓名
            $name = $rowData[$startIndex++];
            // 性别
            $sex = $rowData[$startIndex++];
            // 体育项
            $item1 = $rowData[$startIndex++];
            // 成绩
            $score1 = $rowData[$startIndex++];
            $item2 = $rowData[$startIndex++];
            $score2 = $rowData[$startIndex++];

            $item3 = $rowData[$startIndex++];
            $score3 = $rowData[$startIndex++];
            $item4 = $rowData[$startIndex++];
            $score4 = $rowData[$startIndex++];
            
            // 得分
            $point1 = 0;
            $point2 = 0;
            $point3 = 0;
            $point4 = 0;
            // 总得分
            $totalPoint = 0;
            // 如果成绩栏位留的是空时
            $score1 = $score1 != '' ? $score1 : '0';
            $score2 = $score2 != '' ? $score2 : '0';
            $score3 = $score3 != '' ? $score3 : '0';
            $score4 = $score4 != '' ? $score4 : '0';
            if (!empty($sex)
              && !empty($item1) && $score1 != ''
              && !empty($item2) && $score2 != ''
              && !empty($item3) && $score3 != ''
              && !empty($item4) && $score4 != ''
            ) {
                $sextmp = $this->sexs[$sex];
                // 计算成绩
                if (isset($sports[$sextmp . "-" . $item1])) {
                    // 转换输入的成绩和要显示的
                    $scoreInfo = $sports[$sextmp . "-" . $item1];
                    $scoreRes = $scoreLib->convertScore($score1, $scoreInfo['unit'], $scoreInfo['show_unit']);
                    $calScore = $scoreRes[0];
                    // 计算完毕后把展示的重新赋值回去 
                    $score1 = strval($scoreRes[1] . " ");
                    $point1 = $scoreLib->calPoint($scoreInfo['score_setting'], $calScore);
                    $totalPoint += $point1;
                }
                if (isset($sports[$sextmp . "-" . $item2])) {
                    $scoreInfo = $sports[$sextmp . "-" . $item2];
                    $scoreRes = $scoreLib->convertScore($score2, $scoreInfo['unit'], $scoreInfo['show_unit']);
                    $calScore = $scoreRes[0];
                    $score2 = strval($scoreRes[1] . " ");
                    $point2 = $scoreLib->calPoint($scoreInfo['score_setting'], $calScore);
                    $totalPoint += $point2;
                }
                if (isset($sports[$sextmp . "-" . $item3])) {
                    $scoreInfo = $sports[$sextmp . "-" . $item3];
                    $scoreRes = $scoreLib->convertScore($score3, $scoreInfo['unit'], $scoreInfo['show_unit']);
                    $calScore = $scoreRes[0];
                    $score3 = strval($scoreRes[1] . " ");
                    $point3 = $scoreLib->calPoint($scoreInfo['score_setting'], $calScore);
                    $totalPoint += $point3;
                }
                if (isset($sports[$sextmp . "-" . $item4])) {
                    $scoreInfo = $sports[$sextmp . "-" . $item4];
                    $scoreRes = $scoreLib->convertScore($score4, $scoreInfo['unit'], $scoreInfo['show_unit']);
                    $calScore = $scoreRes[0];
                    $score4 = strval($scoreRes[1] . " " );
                    $point4 = $scoreLib->calPoint($scoreInfo['score_setting'], $calScore);
                    $totalPoint += $point4;
                }
                $calRes = $scoreLib->calMiddleScore(null, $fixedScore, null, $middleLevelSetting, $totalPoint, $middlePercent);
                $data[] = $grade;
                $data[] = $name;
                $data[] = $sex;
                $data[] = $item1;
                $data[] = $score1;
                $data[] = $point1;

                $data[] = $item2;
                $data[] = $score2;
                $data[] = $point2;

                $data[] = $item3;
                $data[] = $score3;
                $data[] = $point3;

                $data[] = $item4;
                $data[] = $score4;
                $data[] = $point4;

                $data[] = $point1 + $point2 + $point3 + $point4;
                $data[] = $calRes['score_total'];
                $data[] = $calRes['level_code'] . '('. $calRes['level_name'] . ')';
                // $data[] = $calRes['level_name'];
                $newExcelData[] = $data;
            }
        }
        $this->createXlsMiddle($areaId, $type, $filename, $newExcelData);
    }
    
    // 中考模版
    private function createXlsMiddle($areaId, $type, $filename, $excelData = null)
    {
        $areaModel = AreaModel::where('id', $areaId)->find();
        $sheetName = "钧韬竞速" . $areaModel->area_name . $this->scoreTypes[$type] ."成绩换算表";
        // 查询体育类别
        $items = [];
        $sportTypes = ScoreTypeModel::where('sport_area_id', $areaId)->where('status', 'normal')->select();
        // 体育项
        $sportItmes = [];
        // 样例行
        $showExample = [];
        foreach($sportTypes as $st) {
            $items[] = $st->type_name;
            $items[] = '成绩';
            // 有得分的
            if (!empty($excelData)) {
                $items[] = '得分';
            }
            $sportModel = ScoreModel::where('sport_area_id', $areaId)
              ->where('sport_type_id', $st->id)
              ->where('score_type', $type)
              ->where('status', 'normal')
              ->order('weigh', 'desc')
              ->select();
            $sports = [];
            foreach($sportModel as $si) {
                if (!in_array($si->project_name, $sports)) {
                    $sports[] = $si->project_name;
                }
                if (empty($excelData) && $si->show_example) {
                    $showExample[] = '例如：' . $si->project_name;
                    $showExample[] = '例如：' . $si->show_val;
                }
            }
            $sportItems[] = $sports;

        }
        // 有成绩时补充的后面的列
        $colNames = [];
        if (!empty($excelData)) {
            $colNames = ['四项合计', '换算成绩', '评级'];
        }
        $rowArray = array_merge(['班级', '姓名','性别'], $items, $colNames);
        //实例化一个新的excel文档
        $newExcel = new Spreadsheet();
        //获取当前操作sheet的对象
        $objSheet = $newExcel->getActiveSheet();
        $objSheet->setTitle($sheetName);//设置当前sheet的标题
        // 中考模版
        $rowNum = 1; 
        // 以下是首行样式
        $objSheet->setCellValue('A1', $sheetName);
        // 结束的列
        $colEndName = "K";
        // 有计算结果用
        if (!empty($excelData)) {
            $colEndName = "R";
        }
        // 增加输入的注释说明
        if (empty($excelData)) {
            $objSheet->setCellValue('L1', '分钟的输入格式以"."隔开,例如：2分56秒12输入2.56.12  3分10秒输入3.10');
            $objSheet->getStyle('L1')->applyFromArray([
                'font' => [
                    'bold' => true,
                    'size' => 12,
                    'color'=>['argb' => 'fd3838']
                ],
            ]);
            /*
            // 样例行
            $objSheet->setCellValue('L3', '参考样例行，请勿编辑和删除');
            $objSheet->getStyle('L3')->applyFromArray([
                'font' => [
                    'bold' => true,
                    'size' => 12,
                    'color'=>['argb' => 'fd3838']
                ],
            ]);
            */
        }
        // 合并单元格
        $objSheet->mergeCells('A' . $rowNum.':'.$colEndName.''.$rowNum);
        // 行样式
        $objSheet->getStyle('A'.$rowNum.':'.'A'.$rowNum)->applyFromArray([
            'font' => [
                'bold' => true,
                'size' => 18,
                'color'=>['argb' => '7030a0']
            ],
        ]);
        // 设置合并列的行高
        $objSheet->getRowDimension($rowNum)->setRowHeight(40);
        $objSheet->getStyle('A' . $rowNum)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $objSheet->getStyle('A' . $rowNum)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        // 第二行
        $rowNum++;
        // 固定首行内容
        if (empty($excelData)) {
            $rowExampleArray = array_merge(['例如：10班', '例如：张三','例如：男'], $showExample);
            foreach ($rowArray as $key => &$ra) {
                // 使用 \n 分隔标题和示例，但不直接合并
                $parts = [$ra, $rowExampleArray[$key]];
                $ra = implode("\n", $parts);
            }
            unset($ra);
            $objSheet->fromArray($rowArray, NULL, 'A' . $rowNum);
            
            // 设置自动换行
            $objSheet->getStyle('A' . $rowNum . ':' . $colEndName . $rowNum)
                    ->getAlignment()
                    ->setWrapText(true);
                    
            // 使用富文本格式设置不同的字体大小
            foreach(range('A', $colEndName) as $col) {
                $cell = $objSheet->getCell($col . $rowNum);
                $text = $cell->getValue();
                if (strpos($text, "\n") !== false) {
                    list($title, $example) = explode("\n", $text);
                    $rich = new \PhpOffice\PhpSpreadsheet\RichText\RichText();
                    $part1 = $rich->createTextRun($title . "\n");
                    $part1->getFont()->setSize(14)->setColor(new \PhpOffice\PhpSpreadsheet\Style\Color('FFFFFF')); // 标题字体大小
                    $part2 = $rich->createTextRun($example);
                    $part2->getFont()->setSize(10)->setColor(new \PhpOffice\PhpSpreadsheet\Style\Color('FFFFFF')); // 示例字体大小
                    $cell->setValue($rich);
                }
            }
            // 设置行高
            $objSheet->getRowDimension($rowNum)->setRowHeight(36);
        } else {
            $objSheet->fromArray($rowArray, NULL, 'A' . $rowNum);
            // 设置行高
            $objSheet->getRowDimension($rowNum)->setRowHeight(26);
        }
        $objSheet->getStyle('A' . $rowNum . ':' . $colEndName . $rowNum)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('7030a0');
        $objSheet->getStyle('A' . $rowNum . ':' . $colEndName . $rowNum)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $objSheet->getStyle('A' . $rowNum . ':' . $colEndName . $rowNum)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $objSheet->getStyle('A' . $rowNum . ':' . $colEndName . $rowNum)->applyFromArray([
            'font' => [
                'bold' => true,
                'size' => 14,
                'color'=>['argb' => 'ffffff']
            ],
        ]);
        $objSheet->getStyle('A' . $rowNum . ':' . $colEndName . $rowNum)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        $objSheet->freezePane('A' . ($rowNum + 1));

        // 开始后面的行
        $rowNum++;
        // 当没有计算结果时
        if (empty($excelData)) { 
            // 生成下拉
            for($row=$rowNum; $row< 1000; $row++) {
                // 生成黑线
                $objSheet->getStyle('A' . $row . ':' . $colEndName . $row)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
                $this->setCell($objSheet, 'C' . $row, ["男", "女"]);
                $this->setCell($objSheet, 'D' . $row, $sportItems[0]);
                $this->setCell($objSheet, 'F' . $row, $sportItems[1]);
                $this->setCell($objSheet, 'H' . $row, $sportItems[2]);
                $this->setCell($objSheet, 'J' . $row, $sportItems[3]);
            }
            // 保存区域和考试类别
            $objSheet->setCellValue('A8130', $areaId);
            $objSheet->setCellValue('B8130', $type);
        } else {
            $font = [
                'font' => [
                    'bold' => true,
                    'size' => 11,
                    'color'=>['argb' => 'ff0000']
                 ],
            ];
            $rowCount = count($excelData) + $rowNum;
            // 有计算结果时
            $objSheet->fromArray($excelData, NULL, 'A' . $rowNum);
            for ($row = $rowNum; $row < $rowCount; $row++) {
                // 生成黑线
                $objSheet->getStyle('A' . $row . ':' . $colEndName . $row)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
                $objSheet->getStyle('F'.$row)->applyFromArray($font);
                $objSheet->getStyle('I'.$row)->applyFromArray($font);
                $objSheet->getStyle('L'.$row)->applyFromArray($font);
                $objSheet->getStyle('O'.$row)->applyFromArray($font);
                $objSheet->getStyle('P'.$row)->applyFromArray($font);
                $objSheet->getStyle('Q'.$row)->applyFromArray($font);
                $objSheet->getStyle('R'.$row)->applyFromArray($font);
            }
        }

        // 设置整体列宽
        $objSheet->getDefaultColumnDimension()->setWidth(12);
        // 设置整体居中
        $objSheet->getParent()->getDefaultStyle()->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $writer = new Xlsx($newExcel);
        $writer->save($filename);
    
    }


    private function setCell($objSheet, $cellName, $formulas)
    {
        $formula = implode(",", $formulas);
        $formula = '"'.$formula.'"';
        $validation = $objSheet->getCell($cellName)->getDataValidation();
        $validation->setType(DataValidation::TYPE_LIST);
        $validation->setErrorStyle(DataValidation::STYLE_STOP);
        $validation->setAllowBlank(false);
        $validation->setShowInputMessage(true);
        $validation->setShowErrorMessage(true);
        $validation->setShowDropDown(true);
        $validation->setErrorTitle('错误提示');
        $validation->setError('输入的值不在下拉选项内');
        $validation->setPromptTitle('请选择');
        // $validation->setFormula1('"男,女"');
        $validation->setFormula1($formula);
    }
}
